﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CRMFramework;
using System.Data;
using Newtonsoft.Json;
using System.Xml;
using MySql.Data.MySqlClient;

namespace CRMBiz.DataImportHandler
{
    public static class GetDataFromMfFHandler
    {

        public static Boolean InsertMFDataToDB(string insertstr)
        {
            Boolean insertresult = false;
            try
            {
                DBhelper.ExecuteNonQuery(insertstr);
                insertresult = true;
            }
            catch (Exception)
            {
                throw;
            }
            return insertresult;
        }

        public static Boolean checkApplicationExist(string jsonstring, string operatorname)
        {
            DataSet ds = new DataSet();
            bool insertresult = false;
            try
            {
                ds = ConvertJsontoDataSet(jsonstring);
                var para = new[] {
                    DBhelper.SetParameter("@IdenticalID", ds.Tables["crm_application"].Rows[0]["app_id"].ToString()),
                    DBhelper.SetParameter("@comesfrom", ds.Tables["crm_application"].Rows[0]["comesfrom"].ToString())
                };
                DataSet dsAppId = DBhelper.ExecuteDataset("select app_id from crm_application where IdenticalID=@IdenticalID and comesfrom=@comesfrom", para);
                if (dsAppId.Tables[0].Rows.Count > 0)
                    insertresult = UpdateMaxFundingDataToDB(ds, operatorname);
                else
                    insertresult = InsertMaxFundingDataToDB(ds, operatorname);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return insertresult;
        }

        //MaxFunding Insert
        public static bool InsertMaxFundingDataToDB(DataSet ds, string operatorname)
        {
            int app_id = 0, contactid = 0, currentcontactid = 0, counter = 0;
            string applicationResult = string.Empty, comesfrom = string.Empty;
            bool insertresult = false;
            StringBuilder sbSql = new StringBuilder();
            MySqlConnection cn = new MySqlConnection(DBhelper.DBConnectionString);
            MySqlCommand cmd;
            cn.Open();
            MySqlTransaction transaction = cn.BeginTransaction();
            try
            {
                if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Pass")
                    applicationResult = "1.1  Await documents 1";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Support Required")
                    applicationResult = "1.2 Await joint application";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Manual Respond")
                    applicationResult = "1.3 Await manual process";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Withdrawn")
                    applicationResult = "5.3 Withdrawn - Eligible";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Not Eligible(Support Required)" || ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Not Eligible")
                    applicationResult = "1.5 Not eligible";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Not Eligible(iDecision: Pass)")
                    applicationResult = "5.3 Withdrawn - Eligible";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Duplicated")
                    applicationResult = "Declined By iDecision -  Manipulation detected";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Blacklisted by iDecision")
                    applicationResult = "5.5 Blacklist - Bad Client";
                else
                    applicationResult = "99:Imported-Yet To Allocate";

                //crm_application table
                #region crm_application
                if (ds.Tables["crm_application"] != null && ds.Tables["crm_application"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["crm_application"].Rows)
                    {
                        sbSql.Append("INSERT INTO crm_application(deletedFlag,app_result,LoadNo,Folder,NoInApplication,OfferedAmount,DesiredAmount,amountMoto,amoutProperties,TextAwaitDoc,PurposeSummary,Assets,Memo,LenderID,OutSrcLenderVal,ReferrerID,ReferrerVal,CreatedDate,EditDate,comesfrom,securities,IsUSB,IsCarOwner,status,MC_process,CreatedTime,mc_preferred_loan_period,mc_ipaddress,mc_income_status,mc_asset_status,mc_credit_status,mc_final_status,mc_ClientUpdateTime,mc_purposeType,mc_OtherPurpose,mc_Update_Time,mc_agreement,mc_browser,mc_referrer_company,mc_referrer_email,loanpurposereason,filledout,prevclient,purposedetail,boardingfee,livingfee,totalincome,totalexpense,totalexpensecorrect,disposableincome,ELA,formviewnotice,whyus,timecost,preappcode,template,afterpage,IdenticalID)VALUES(");
                        sbSql.AppendFormat("{0},", 0);
                        sbSql.AppendFormat("'{0}',", applicationResult);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("NoInApplication"))
                            sbSql.AppendFormat("'{0}',", dr["NoInApplication"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("OfferedAmount"))
                            sbSql.AppendFormat("'{0}',", dr["OfferedAmount"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("DesiredAmount"))
                            sbSql.AppendFormat("'{0}',", dr["DesiredAmount"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("amountMoto"))
                            sbSql.AppendFormat("'{0}',", dr["amountMoto"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", 0);
                        if (ds.Tables["crm_application"].Columns.Contains("amoutProperties"))
                            sbSql.AppendFormat("'{0}',", dr["amoutProperties"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", 0);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("PurposeSummary"))
                            sbSql.AppendFormat("'{0}',", dr["PurposeSummary"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("Assets"))
                            sbSql.AppendFormat("'{0}',", dr["Assets"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0},", 0);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("ReferrerID"))
                            sbSql.AppendFormat("'{0}',", dr["ReferrerID"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("ReferrerVal"))
                            sbSql.AppendFormat("'{0}',", dr["ReferrerVal"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                        //set value of comesfrom column
                        sbSql.AppendFormat("'{0}',", dr["comesfrom"].ToString());
                        comesfrom = dr["comesfrom"].ToString();

                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("IsUSB"))
                            sbSql.AppendFormat("'{0}',", dr["IsUSB"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", 0);
                        sbSql.AppendFormat("'{0}',", "Openning");
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("HH:mm:ss"));
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_ipaddress"))
                            sbSql.AppendFormat("'{0}',", dr["mc_ipaddress"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_income_status"))
                            sbSql.AppendFormat("'{0}',", dr["mc_income_status"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_asset_status"))
                            sbSql.AppendFormat("'{0}',", dr["mc_asset_status"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_credit_status"))
                            sbSql.AppendFormat("'{0}',", dr["mc_credit_status"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_final_status"))
                            sbSql.AppendFormat("'{0}',", dr["mc_final_status"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("HH:mm:ss"));
                        if (ds.Tables["crm_application"].Columns.Contains("mc_purposeType"))
                            sbSql.AppendFormat("'{0}',", dr["mc_purposeType"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_OtherPurpose"))
                            sbSql.AppendFormat("'{0}',", dr["mc_OtherPurpose"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_browser"))
                            sbSql.AppendFormat("'{0}',", dr["mc_browser"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_referrer_company"))
                            sbSql.AppendFormat("'{0}',", dr["mc_referrer_company"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("mc_referrer_email"))
                            sbSql.AppendFormat("'{0}',", dr["mc_referrer_email"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("loanpurposereason"))
                            sbSql.AppendFormat("'{0}',", dr["loanpurposereason"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("filledout"))
                            sbSql.AppendFormat("'{0}',", dr["filledout"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("prevclient"))
                            sbSql.AppendFormat("'{0}',", dr["prevclient"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("purposedetail"))
                            sbSql.AppendFormat("'{0}',", dr["purposedetail"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("boardingfee"))
                            sbSql.AppendFormat("'{0}',", dr["boardingfee"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("livingfee"))
                            sbSql.AppendFormat("'{0}',", dr["livingfee"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("totalincome"))
                            sbSql.AppendFormat("'{0}',", dr["totalincome"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("totalexpense"))
                            sbSql.AppendFormat("'{0}',", dr["totalexpense"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("totalexpensecorrect"))
                            sbSql.AppendFormat("'{0}',", dr["totalexpensecorrect"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("disposableincome"))
                            sbSql.AppendFormat("'{0}',", dr["disposableincome"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("ELA"))
                            sbSql.AppendFormat("'{0}',", dr["ELA"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("formviewnotice"))
                            sbSql.AppendFormat("'{0}',", dr["formviewnotice"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("whyus"))
                            sbSql.AppendFormat("'{0}',", dr["whyus"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("timecost"))
                            sbSql.AppendFormat("'{0}',", dr["timecost"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("preappcode"))
                            sbSql.AppendFormat("'{0}',", dr["preappcode"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("template"))
                            sbSql.AppendFormat("'{0}',", dr["template"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("afterpage"))
                            sbSql.AppendFormat("'{0}',", dr["afterpage"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_application"].Columns.Contains("app_id"))
                            sbSql.AppendFormat("'{0}');", dr["app_id"].ToString());
                        else
                            sbSql.AppendFormat("'{0}');", DBNull.Value);
                    }
                    cmd = new MySqlCommand(sbSql.ToString(), cn, transaction);
                    cmd.ExecuteNonQuery();
                    app_id = (int)cmd.LastInsertedId;
                    sbSql = new StringBuilder();

                    sbSql.Append("INSERT INTO crm_applicationappform(ApplicationId,AppFormId,ComesFrom)VALUES(");
                    sbSql.AppendFormat("{0},", app_id);
                    sbSql.AppendFormat("{0},", ds.Tables["crm_application"].Rows[0]["app_id"].ToString());
                    sbSql.AppendFormat("'{0}');", comesfrom);
                }

                #endregion

                //max_client table
                #region crm_contact
                if (ds.Tables["crm_contact"] != null && ds.Tables["crm_contact"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["crm_contact"].Rows)
                    {
                        sbSql.Append("INSERT INTO crm_contact(FirstName,MiddleName,SurName,Title,Gender,Mobile,HomePhone,Email,DriverLicense,")
                        .Append("Citizenship,DateBirth,StreetNo,Street,Suburb,State,PostCode,ResidencePeriod,ResidenceLength,Dependant,")
                        .Append("app_ResidentialStatus,app_number,app_id,NumberFromAppinfo,Memo,EditDate,DupwithContact,deletedFlag,Role,Relationship,")
                        .Append("WorkPhone,FaxNumber,secondEmailAddress,PostalAddress,IncomeType,SelfEmpTypeofEntity,SelfEmpBusinessName,")
                        .Append("SelfEmpABN,SelfEmpNatureofBusiness,SelfEmpPhone,SelfEmpAddress,KinsName,KinsRelationship,KinsContactNumber,")
                        .Append("Notes,EmpEmployer,EmpOccupation,EmpJobPosition,EmpEmployerPhone,EmpContactPerson,EmpWorkAddress,comesfrom,Unsubscribe_Ad,")
                        .Append("CreateDate,Sacc_accountlock)VALUES(");

                        if (ds.Tables["crm_contact"].Columns.Contains("FirstName"))
                            sbSql.AppendFormat("'{0}',", dr["FirstName"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("MiddleName"))
                            sbSql.AppendFormat("'{0}',", dr["MiddleName"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("SurName"))
                            sbSql.AppendFormat("'{0}',", dr["SurName"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Title"))
                            sbSql.AppendFormat("'{0}',", dr["Title"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Gender"))
                            sbSql.AppendFormat("'{0}',", dr["Gender"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Mobile"))
                            sbSql.AppendFormat("'{0}',", dr["Mobile"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("HomePhone"))
                            sbSql.AppendFormat("'{0}',", dr["HomePhone"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Email"))
                            sbSql.AppendFormat("'{0}',", dr["Email"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("DriverLicense"))
                            sbSql.AppendFormat("'{0}',", dr["DriverLicense"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Citizenship"))
                            sbSql.AppendFormat("'{0}',", dr["Citizenship"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("DateBirth"))
                        {
                            if (Convert.ToString(dr["DateBirth"]) == "0000-00-00")
                                sbSql.AppendFormat("'{0}',", "1900-01-01");
                            else
                                sbSql.AppendFormat("'{0}',", DateTime.Parse(dr["DateBirth"].ToString()).ToString("yyyy-MM-dd"));
                        }
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("StreetNo"))
                            sbSql.AppendFormat("'{0}',", dr["StreetNo"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Street"))
                            sbSql.AppendFormat("'{0}',", dr["Street"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Suburb"))
                            sbSql.AppendFormat("'{0}',", dr["Suburb"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("State"))
                            sbSql.AppendFormat("'{0}',", dr["State"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Postcode"))
                            sbSql.AppendFormat("'{0}',", dr["Postcode"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("ResidencePeriod"))
                            sbSql.AppendFormat("'{0}',", dr["ResidencePeriod"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("ResidenceLength"))
                            sbSql.AppendFormat("'{0}',", dr["ResidenceLength"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Dependant"))
                            sbSql.AppendFormat("'{0}',", dr["Dependant"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("app_ResidentialStatus"))
                            sbSql.AppendFormat("'{0}',", dr["app_ResidentialStatus"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", 0);
                        if (ds.Tables["crm_contact"].Columns.Contains("app_id"))
                            sbSql.AppendFormat("'{0}',", int.Parse(dr["app_id"].ToString()));
                        else
                            sbSql.AppendFormat("'{0}',", 0);
                        if (ds.Tables["crm_contact"].Columns.Contains("NumberFromAppinfo"))
                            sbSql.AppendFormat("'{0}',", dr["NumberFromAppinfo"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        sbSql.AppendFormat("'{0}',", 0);
                        sbSql.AppendFormat("'{0}',", 0);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("Relationship"))
                            sbSql.AppendFormat("'{0}',", dr["Relationship"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("WorkPhone"))
                            sbSql.AppendFormat("'{0}',", dr["WorkPhone"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("FaxNumber"))
                            sbSql.AppendFormat("'{0}',", dr["FaxNumber"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("secondEmailAddress"))
                            sbSql.AppendFormat("'{0}',", dr["secondEmailAddress"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_contact"].Columns.Contains("PostalAddress"))
                            sbSql.AppendFormat("'{0}',", dr["PostalAddress"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        sbSql.AppendFormat("'{0}',", 0);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        sbSql.AppendFormat("'{0}');", 0);

                        cmd = new MySqlCommand(sbSql.ToString(), cn, transaction);
                        cmd.ExecuteNonQuery();

                        contactid = (int)cmd.LastInsertedId;
                        if (currentcontactid == 0)
                            currentcontactid = contactid;
                        sbSql = new StringBuilder();

                        sbSql.Append("INSERT INTO crm_applicationcontact(app_id,ContactID,NoInApplication,CombineApp,deletedFlag)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        sbSql.AppendFormat("{0},", contactid);
                        sbSql.AppendFormat("{0},", ++counter);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0});", 0);

                        //for crm_applicationcredit
                        if (ds.Tables["crm_applicationcredit"] != null && ds.Tables["crm_applicationcredit"].Rows.Count > 0)
                        {
                            DataRow[] drapplicationcredit = ds.Tables["crm_applicationcredit"].Select("app_id=" + dr["app_id"].ToString() + " and CurrentContact_id=" + dr["ContactID"].ToString());
                            if (drapplicationcredit.Length > 0)
                            {
                                sbSql.Append("INSERT INTO crm_applicationcredit(app_id,CurrentContact_id,OriginalContact_id,credit_hardship,credit_gambling,credit_claims,credit_history,credit_outstanding,credit_dischargedate,credit_discharge,CreatedDate,EditDate,Comesfrom,WhyChooseMF,ExistingClient,IdenticalID)VALUES(");
                                sbSql.AppendFormat("{0},", app_id);
                                sbSql.AppendFormat("{0},", contactid);
                                sbSql.AppendFormat("{0},", contactid);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_hardship"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_hardship"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_gambling"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_gambling"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_claims"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_claims"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_history"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_history"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_outstanding"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_outstanding"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_dischargedate"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_dischargedate"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("credit_discharge"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["credit_discharge"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                sbSql.AppendFormat("'{0}',", comesfrom);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("WhyChooseMF"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["WhyChooseMF"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("ExistingClient"))
                                    sbSql.AppendFormat("'{0}',", drapplicationcredit[0]["ExistingClient"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_applicationcredit"].Columns.Contains("app_id"))
                                    sbSql.AppendFormat("'{0}');", dr["app_id"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}');", DBNull.Value);
                            }
                        }

                        //crm_income table
                        if (ds.Tables["crm_income"] != null && ds.Tables["crm_income"].Rows.Count > 0)
                        {
                            DataRow[] drincome = ds.Tables["crm_income"].Select("app_id=" + dr["app_id"].ToString() + " and CurrentContact_id=" + dr["ContactID"].ToString());
                            if (drincome.Length > 0)
                            {
                                sbSql.Append("INSERT INTO crm_income(app_id,CurrentContact_id,OriginalContact_id,income_aftertax,income_aftertax_period,income_centrelink,income_other,income_other_period,income_rental,income_total,CreatedDate,EditDate,Comesfrom,OtherIncomeSpecify,income_other_type,IdenticalID)VALUES(");
                                sbSql.AppendFormat("{0},", app_id);
                                sbSql.AppendFormat("{0},", contactid);
                                sbSql.AppendFormat("{0},", contactid);
                                if (ds.Tables["crm_income"].Columns.Contains("income_aftertax"))
                                    sbSql.AppendFormat("{0},", drincome[0]["income_aftertax"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_aftertax_period"))
                                    sbSql.AppendFormat("'{0}',", drincome[0]["income_aftertax_period"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_centrelink"))
                                    sbSql.AppendFormat("{0},", drincome[0]["income_centrelink"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_other"))
                                    sbSql.AppendFormat("{0},", drincome[0]["income_other"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_other_period"))
                                    sbSql.AppendFormat("'{0}',", drincome[0]["income_other_period"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_rental"))
                                    sbSql.AppendFormat("'{0}',", drincome[0]["income_rental"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_total"))
                                    sbSql.AppendFormat("'{0}',", drincome[0]["income_total"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd"));
                                sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd"));
                                sbSql.AppendFormat("'{0}',", comesfrom);
                                if (ds.Tables["crm_income"].Columns.Contains("OtherIncomeSpecify"))
                                    sbSql.AppendFormat("'{0}',", drincome[0]["OtherIncomeSpecify"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("income_other_type"))
                                    sbSql.AppendFormat("'{0}',", drincome[0]["income_other_type"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}',", DBNull.Value);
                                if (ds.Tables["crm_income"].Columns.Contains("app_id"))
                                    sbSql.AppendFormat("'{0}');", dr["app_id"].ToString());
                                else
                                    sbSql.AppendFormat("'{0}');", DBNull.Value);
                            }
                        }
                    }
                }
                #endregion

                //crm_business table
                #region crm_business
                if (ds.Tables["crm_business"] != null && ds.Tables["crm_business"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["crm_business"].Rows)
                    {
                        sbSql.Append("INSERT INTO crm_business(AppID,Stage,Entity,Relationship1,Relationship2,ABN,Nature,Address,Website,MoneyDetail,Income,Repay,RepayBy,RepayPeriod,Trustee,Beneficiary,CashSale,EftposSale,UsbYear,UsbMonth,CompanyName,CompanySecurity,SecurityStatus,RegistrationDate,PriorChargee,SubsequentChargee,CompanyComment,LoanID,OriginalContact_id,CurrentContact_id,comesfrom,IdenticalID)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        if (ds.Tables["crm_business"].Columns.Contains("Stage"))
                            sbSql.AppendFormat("'{0}',", dr["Stage"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Entity"))
                            sbSql.AppendFormat("'{0}',", dr["Entity"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Relationship1"))
                            sbSql.AppendFormat("'{0}',", dr["Relationship1"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Relationship2"))
                            sbSql.AppendFormat("'{0}',", dr["Relationship2"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("ABN"))
                            sbSql.AppendFormat("'{0}',", dr["ABN"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Nature"))
                            sbSql.AppendFormat("'{0}',", dr["Nature"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Address"))
                            sbSql.AppendFormat("'{0}',", dr["Address"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Website"))
                            sbSql.AppendFormat("'{0}',", dr["Website"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("MoneyDetail"))
                            sbSql.AppendFormat("'{0}',", dr["MoneyDetail"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Income"))
                            sbSql.AppendFormat("'{0}',", dr["Income"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Repay"))
                            sbSql.AppendFormat("'{0}',", dr["Repay"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("RepayBy"))
                            sbSql.AppendFormat("'{0}',", dr["RepayBy"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("RepayPeriod"))
                            sbSql.AppendFormat("'{0}',", dr["RepayPeriod"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Trustee"))
                            sbSql.AppendFormat("'{0}',", dr["Trustee"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("Beneficiary"))
                            sbSql.AppendFormat("'{0}',", dr["Beneficiary"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("CashSale"))
                            sbSql.AppendFormat("'{0}',", dr["CashSale"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("EftposSale"))
                            sbSql.AppendFormat("'{0}',", dr["EftposSale"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("UsbYear"))
                            sbSql.AppendFormat("'{0}',", dr["UsbYear"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("UsbMonth"))
                            sbSql.AppendFormat("'{0}',", dr["UsbMonth"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_business"].Columns.Contains("CompanyName"))
                            sbSql.AppendFormat("'{0}',", dr["CompanyName"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd"));
                        sbSql.AppendFormat("{0},", 0.00);
                        sbSql.AppendFormat("{0},", 0.00);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0},", 0);
                        sbSql.AppendFormat("{0},", currentcontactid);
                        sbSql.AppendFormat("{0},", currentcontactid);
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        if (ds.Tables["crm_business"].Columns.Contains("AppID"))
                            sbSql.AppendFormat("'{0}');", dr["AppID"].ToString());
                        else
                            sbSql.AppendFormat("'{0}');", DBNull.Value);
                    }
                }
                #endregion

                //usb_info table
                #region usb_info
                if (ds.Tables["usb_info"] != null && ds.Tables["usb_info"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["usb_info"].Rows)
                    {
                        sbSql.Append("INSERT INTO usb_info(usb_appid,usb_ref1name,usb_ref1contact,usb_ref2name,usb_ref2contact,usb_item1,usb_value1,usb_item2,usb_value2,usb_item3,usb_value3,usb_item4,usb_value4,usb_guarantor,usb_otherIncome,comesfrom,IdenticalID)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref1name"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref1name"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref1contact"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref1contact"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref2name"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref2name"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref2contact"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref2contact"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item1"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item1"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value1"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value1"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item2"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item2"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value2"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value2"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item3"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item3"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value3"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value3"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item4"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item4"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value4"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value4"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_guarantor"))
                            sbSql.AppendFormat("'{0}',", dr["usb_guarantor"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_otherIncome"))
                            sbSql.AppendFormat("'{0}',", dr["usb_otherIncome"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_appid"))
                            sbSql.AppendFormat("'{0}');", dr["usb_appid"].ToString());
                        else
                            sbSql.AppendFormat("'{0}');", DBNull.Value);
                    }
                }
                #endregion

                //crm_liabilities table
                #region crm_liabilities
                if (ds.Tables["crm_liabilities"] != null && ds.Tables["crm_liabilities"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["crm_liabilities"].Rows)
                    {
                        sbSql.Append("INSERT INTO crm_liabilities(app_id,CurrentContact_id,OriginalContact_id,liabilities_type,liabilities_owing,liabilities_loanrepayment_payment,liabilities_loanrepayment_paymentperiod,liabilities_creditcard_payment,liabilities_creditcard_paymentperiod,liabilities_boardingrentingfee_payment,liabilities_boardingrentingfee_paymentperiod,liabilities_livingexpenses_payment,liabilities_livingexpenses_paymentperiod,liabilities_bank,CreatedDate,EditDate,Comesfrom,IdenticalID)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        sbSql.AppendFormat("{0},", currentcontactid);
                        sbSql.AppendFormat("{0},", currentcontactid);
                        if (ds.Tables["crm_liabilities"].Columns.Contains("liabilities_type"))
                            sbSql.AppendFormat("'{0}',", dr["liabilities_type"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_liabilities"].Columns.Contains("liabilities_owing"))
                            sbSql.AppendFormat("'{0}',", dr["liabilities_owing"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_liabilities"].Columns.Contains("liabilities_loanrepayment_payment"))
                            sbSql.AppendFormat("'{0}',", dr["liabilities_loanrepayment_payment"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_liabilities"].Columns.Contains("liabilities_bank"))
                            sbSql.AppendFormat("'{0}',", dr["liabilities_bank"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd"));
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd"));
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        if (ds.Tables["crm_liabilities"].Columns.Contains("app_id"))
                            sbSql.AppendFormat("'{0}');", dr["app_id"].ToString());
                        else
                            sbSql.AppendFormat("'{0}');", DBNull.Value);

                    }
                }
                #endregion

                //crm_property table
                #region crm_property
                if (ds.Tables["crm_property"] != null && ds.Tables["crm_property"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["crm_property"].Rows)
                    {
                        sbSql.Append("INSERT INTO crm_property(AppID,Address,Arrears,Owner,PropertyType,Description,LandSize,Bedroom,Bathroom,MortgageOwing,MortgagePayment,MortgageLender,TitleRef,SecurityStatus,InterestType,LodgmentDate,CompletionDate,PropertyValue,ValueSource,Mortgagee,SubSequentParties,PropertyComment,LoanID,Comefrom,IdenticalID,OriginalContact_id,CurrentContact_id)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        if (ds.Tables["crm_property"].Columns.Contains("Address"))
                            sbSql.AppendFormat("'{0}',", dr["Address"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("Arrears"))
                            sbSql.AppendFormat("'{0}',", dr["Arrears"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("Owner"))
                            sbSql.AppendFormat("'{0}',", dr["Owner"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("PropertyType"))
                            sbSql.AppendFormat("'{0}',", dr["PropertyType"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("Description"))
                            sbSql.AppendFormat("'{0}',", dr["Description"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("LandSize"))
                            sbSql.AppendFormat("'{0}',", dr["LandSize"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("Bedroom"))
                            sbSql.AppendFormat("'{0}',", dr["Bedroom"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("Bathroom"))
                            sbSql.AppendFormat("'{0}',", dr["Bathroom"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("MortgageOwing"))
                            sbSql.AppendFormat("'{0}',", dr["MortgageOwing"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("MortgagePayment"))
                            sbSql.AppendFormat("'{0}',", dr["MortgagePayment"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_property"].Columns.Contains("MortgageLender"))
                            sbSql.AppendFormat("'{0}',", dr["MortgageLender"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        if (ds.Tables["crm_property"].Columns.Contains("PropertyValue"))
                            sbSql.AppendFormat("{0},", decimal.Parse(dr["PropertyValue"].ToString()));
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0},", 0);
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        if (ds.Tables["crm_property"].Columns.Contains("AppID"))
                            sbSql.AppendFormat("'{0}',", dr["AppID"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0},", currentcontactid);
                        sbSql.AppendFormat("{0});", currentcontactid);
                    }
                }
                #endregion

                //crm_vehicle table
                #region crm_vehicle
                if (ds.Tables["crm_vehicle"] != null && ds.Tables["crm_vehicle"].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables["crm_vehicle"].Rows)
                    {
                        sbSql.Append("INSERT INTO crm_vehicle(AppID,Brand,Model,Owner,Finance,VehicleYear,Insurance,VIN,EngineNo,RegNo,Colour,Badge,Make,VehicleValue,VehicleOwing,VehicleLender,VehiclePayment,ValueSource,OwningSource,SecurityStatus,ExpiryDate,VehicleComment,LoanID,Comefrom,IdenticalID,CurrentContact_id,OriginalContact_id)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("Brand"))
                            sbSql.AppendFormat("'{0}',", dr["Brand"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("Model"))
                            sbSql.AppendFormat("'{0}',", dr["Model"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("Owner"))
                            sbSql.AppendFormat("'{0}',", dr["Owner"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("Finance"))
                            sbSql.AppendFormat("'{0}',", dr["Finance"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("VehicleYear"))
                            sbSql.AppendFormat("'{0}',", dr["VehicleYear"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("Insurance"))
                            sbSql.AppendFormat("'{0}',", dr["Insurance"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("VehicleValue"))
                            sbSql.AppendFormat("'{0}',", dr["VehicleValue"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("VehicleOwing"))
                            sbSql.AppendFormat("'{0}',", dr["VehicleOwing"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("VehicleLender"))
                            sbSql.AppendFormat("'{0}',", dr["VehicleLender"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("VehiclePayment"))
                            sbSql.AppendFormat("'{0}',", dr["VehiclePayment"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0},", 0);
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        if (ds.Tables["crm_vehicle"].Columns.Contains("AppID"))
                            sbSql.AppendFormat("'{0}',", dr["AppID"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("{0},", currentcontactid);
                        sbSql.AppendFormat("{0});", currentcontactid);
                    }
                }
                #endregion

                //crm_history table
                #region crm_history
                if (app_id != 0)
                {
                    sbSql.Append("INSERT INTO crm_history(app_id,HistoryDate,HistoryTime,Action,Operator,Description) values(");
                    sbSql.AppendFormat("{0},", app_id);
                    sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("yyyy-MM-dd"));
                    sbSql.AppendFormat("'{0}',", DateTime.Now.ToString("HH:mm:ss"));
                    sbSql.AppendFormat("'{0}',", "Online submitted");
                    sbSql.AppendFormat("'{0}',", operatorname);
                    sbSql.AppendFormat("'{0}');", "Online application form submitted");
                }
                #endregion

                cmd = new MySqlCommand(sbSql.ToString(), cn, transaction);
                cmd.ExecuteNonQuery();

                transaction.Commit();
                insertresult = true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            cn.Close();
            return insertresult;
        }

        //MaxFunding Update
        public static Boolean UpdateMaxFundingDataToDB(DataSet ds, string operatorname)
        {
            int app_id = 0;
            string applicationResult = string.Empty, comesfrom = string.Empty;
            Boolean insertresult = false;
            StringBuilder sbSql = new StringBuilder();
            MySqlConnection cn = new MySqlConnection(DBhelper.DBConnectionString);
            MySqlCommand cmd;
            cn.Open();
            MySqlTransaction transaction = cn.BeginTransaction();
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@IdenticalID", ds.Tables["crm_application"].Rows[0]["app_id"].ToString()),
                    DBhelper.SetParameter("@comesfrom", ds.Tables["crm_application"].Rows[0]["comesfrom"].ToString())
                };
                DataSet dsAppId = DBhelper.ExecuteDataset("select app_id from crm_application where IdenticalID=@IdenticalID and comesfrom=@comesfrom", para);
                if (dsAppId.Tables[0].Rows.Count > 0)
                    app_id = int.Parse(dsAppId.Tables[0].Rows[0]["app_id"].ToString());

                if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Pass")
                    applicationResult = "1.1  Await documents 1";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Support Required")
                    applicationResult = "1.2 Await joint application";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Manual Respond")
                    applicationResult = "1.3 Await manual process";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Withdrawn")
                    applicationResult = "5.3 Withdrawn - Eligible";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Not Eligible(Support Required)" || ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Not Eligible")
                    applicationResult = "1.5 Not eligible";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Not Eligible(iDecision: Pass)")
                    applicationResult = "5.3 Withdrawn - Eligible";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Duplicated")
                    applicationResult = "Declined By iDecision -  Manipulation detected";
                else if (ds.Tables["crm_application"].Rows[0]["mc_final_status"].ToString() == "Blacklisted by iDecision")
                    applicationResult = "5.5 Blacklist - Bad Client";
                else
                    applicationResult = "99:Imported-Yet To Allocate";

                //crm_application table
                #region crm_application
                if (dsAppId.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables["crm_application"] != null && ds.Tables["crm_application"].Rows.Count > 0)
                    {
                        foreach (DataRow dr in ds.Tables["crm_application"].Rows)
                        {
                            sbSql.AppendFormat("UPDATE crm_application SET deletedFlag={0},", 0);
                            sbSql.AppendFormat("app_result='{0}',", applicationResult);
                            sbSql.AppendFormat("LoadNo='{0}',", DBNull.Value);
                            sbSql.AppendFormat("Folder='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("NoInApplication"))
                                sbSql.AppendFormat("NoInApplication='{0}',", dr["NoInApplication"].ToString());
                            else
                                sbSql.AppendFormat("NoInApplication='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("DesiredAmount"))
                                sbSql.AppendFormat("OfferedAmount='{0}',", dr["DesiredAmount"].ToString());
                            else
                                sbSql.AppendFormat("OfferedAmount='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("DesiredAmount"))
                                sbSql.AppendFormat("DesiredAmount='{0}',", dr["DesiredAmount"].ToString());
                            else
                                sbSql.AppendFormat("DesiredAmount='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("amountMoto"))
                                sbSql.AppendFormat("amountMoto='{0}',", dr["amountMoto"].ToString());
                            else
                                sbSql.AppendFormat("amountMoto='{0}',", 0);
                            if (ds.Tables["crm_application"].Columns.Contains("amoutProperties"))
                                sbSql.AppendFormat("amoutProperties='{0}',", dr["amoutProperties"].ToString());
                            else
                                sbSql.AppendFormat("amoutProperties='{0}',", 0);
                            sbSql.AppendFormat("TextAwaitDoc='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("PurposeSummary"))
                                sbSql.AppendFormat("PurposeSummary='{0}',", dr["PurposeSummary"].ToString());
                            else
                                sbSql.AppendFormat("PurposeSummary='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("Assets"))
                                sbSql.AppendFormat("Assets='{0}',", dr["Assets"].ToString());
                            else
                                sbSql.AppendFormat("Assets='{0}',", DBNull.Value);
                            sbSql.AppendFormat("Memo='{0}',", DBNull.Value);
                            sbSql.AppendFormat("LenderID={0},", 0);
                            sbSql.AppendFormat("OutSrcLenderVal='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("ReferrerID"))
                                sbSql.AppendFormat("ReferrerID='{0}',", dr["ReferrerID"].ToString());
                            else
                                sbSql.AppendFormat("ReferrerID='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("ReferrerVal"))
                                sbSql.AppendFormat("ReferrerVal='{0}',", dr["ReferrerVal"].ToString());
                            else
                                sbSql.AppendFormat("ReferrerVal='{0}',", DBNull.Value);
                            sbSql.AppendFormat("EditDate='{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                            //set value of comesfrom column
                            sbSql.AppendFormat("comesfrom='{0}',", dr["comesfrom"].ToString());
                            comesfrom = dr["comesfrom"].ToString();

                            sbSql.AppendFormat("securities='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("IsUSB"))
                                sbSql.AppendFormat("IsUSB='{0}',", dr["IsUSB"].ToString());
                            else
                                sbSql.AppendFormat("IsUSB='{0}',", DBNull.Value);
                            sbSql.AppendFormat("IsCarOwner='{0}',", 0);
                            sbSql.AppendFormat("status='{0}',", "Openning");
                            sbSql.AppendFormat("MC_process='{0}',", DBNull.Value);
                            sbSql.AppendFormat("mc_preferred_loan_period='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_ipaddress"))
                                sbSql.AppendFormat("mc_ipaddress='{0}',", dr["mc_ipaddress"].ToString());
                            else
                                sbSql.AppendFormat("mc_ipaddress='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_income_status"))
                                sbSql.AppendFormat("mc_income_status='{0}',", dr["mc_income_status"].ToString());
                            else
                                sbSql.AppendFormat("mc_income_status='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_asset_status"))
                                sbSql.AppendFormat("mc_asset_status='{0}',", dr["mc_asset_status"].ToString());
                            else
                                sbSql.AppendFormat("mc_asset_status='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_credit_status"))
                                sbSql.AppendFormat("mc_credit_status='{0}',", dr["mc_credit_status"].ToString());
                            else
                                sbSql.AppendFormat("mc_credit_status='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_final_status"))
                                sbSql.AppendFormat("mc_final_status='{0}',", dr["mc_final_status"].ToString());
                            else
                                sbSql.AppendFormat("mc_final_status='{0}',", DBNull.Value);
                            sbSql.AppendFormat("mc_ClientUpdateTime='{0}',", DateTime.Now.ToString("HH:mm:ss"));
                            if (ds.Tables["crm_application"].Columns.Contains("mc_purposeType"))
                                sbSql.AppendFormat("mc_purposeType='{0}',", dr["mc_purposeType"].ToString());
                            else
                                sbSql.AppendFormat("mc_purposeType='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_OtherPurpose"))
                                sbSql.AppendFormat("mc_OtherPurpose='{0}',", dr["mc_OtherPurpose"].ToString());
                            else
                                sbSql.AppendFormat("mc_OtherPurpose='{0}',", DBNull.Value);
                            sbSql.AppendFormat("mc_Update_Time='{0}',", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                            sbSql.AppendFormat("mc_agreement='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_browser"))
                                sbSql.AppendFormat("mc_browser='{0}',", dr["mc_browser"].ToString());
                            else
                                sbSql.AppendFormat("mc_browser='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_referrer_company"))
                                sbSql.AppendFormat("mc_referrer_company='{0}',", dr["mc_referrer_company"].ToString());
                            else
                                sbSql.AppendFormat("mc_referrer_company='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("mc_referrer_email"))
                                sbSql.AppendFormat("mc_referrer_email='{0}',", dr["mc_referrer_email"].ToString());
                            else
                                sbSql.AppendFormat("mc_referrer_email='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("loanpurposereason"))
                                sbSql.AppendFormat("loanpurposereason='{0}',", dr["loanpurposereason"].ToString());
                            else
                                sbSql.AppendFormat("loanpurposereason='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("filledout"))
                                sbSql.AppendFormat("filledout='{0}',", dr["filledout"].ToString());
                            else
                                sbSql.AppendFormat("filledout='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("prevclient"))
                                sbSql.AppendFormat("prevclient='{0}',", dr["prevclient"].ToString());
                            else
                                sbSql.AppendFormat("prevclient='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("purposedetail"))
                                sbSql.AppendFormat("purposedetail='{0}',", dr["purposedetail"].ToString());
                            else
                                sbSql.AppendFormat("purposedetail='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("boardingfee"))
                                sbSql.AppendFormat("boardingfee='{0}',", dr["boardingfee"].ToString());
                            else
                                sbSql.AppendFormat("boardingfee='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("livingfee"))
                                sbSql.AppendFormat("livingfee='{0}',", dr["livingfee"].ToString());
                            else
                                sbSql.AppendFormat("livingfee='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("totalincome"))
                                sbSql.AppendFormat("totalincome='{0}',", dr["totalincome"].ToString());
                            else
                                sbSql.AppendFormat("totalincome='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("totalexpense"))
                                sbSql.AppendFormat("totalexpense='{0}',", dr["totalexpense"].ToString());
                            else
                                sbSql.AppendFormat("totalexpense='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("totalexpensecorrect"))
                                sbSql.AppendFormat("totalexpensecorrect='{0}',", dr["totalexpensecorrect"].ToString());
                            else
                                sbSql.AppendFormat("totalexpensecorrect='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("disposableincome"))
                                sbSql.AppendFormat("disposableincome='{0}',", dr["disposableincome"].ToString());
                            else
                                sbSql.AppendFormat("disposableincome='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("ELA"))
                                sbSql.AppendFormat("ELA='{0}',", dr["ELA"].ToString());
                            else
                                sbSql.AppendFormat("ELA='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("formviewnotice"))
                                sbSql.AppendFormat("formviewnotice='{0}',", dr["formviewnotice"].ToString());
                            else
                                sbSql.AppendFormat("formviewnotice='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("whyus"))
                                sbSql.AppendFormat("whyus='{0}',", dr["whyus"].ToString());
                            else
                                sbSql.AppendFormat("whyus='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("timecost"))
                                sbSql.AppendFormat("timecost='{0}',", dr["timecost"].ToString());
                            else
                                sbSql.AppendFormat("timecost='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("preappcode"))
                                sbSql.AppendFormat("preappcode='{0}',", dr["preappcode"].ToString());
                            else
                                sbSql.AppendFormat("preappcode='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("template"))
                                sbSql.AppendFormat("template='{0}',", dr["template"].ToString());
                            else
                                sbSql.AppendFormat("template='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("afterpage"))
                                sbSql.AppendFormat("afterpage='{0}',", dr["afterpage"].ToString());
                            else
                                sbSql.AppendFormat("afterpage='{0}',", DBNull.Value);
                            if (ds.Tables["crm_application"].Columns.Contains("app_id"))
                                sbSql.AppendFormat("IdenticalID='{0}'", dr["app_id"].ToString());
                            else
                                sbSql.AppendFormat("IdenticalID='{0}'", DBNull.Value);
                            sbSql.AppendFormat(" where app_id='{0}';", app_id);
                        }
                    }
                }
                #endregion

                //usb_info table
                #region usb_info
                if (ds.Tables["usb_info"] != null && ds.Tables["usb_info"].Rows.Count > 0)
                {
                    sbSql.AppendFormat("delete from usb_info where usb_appid={0};", app_id);
                    foreach (DataRow dr in ds.Tables["usb_info"].Rows)
                    {
                        sbSql.Append("INSERT INTO usb_info(usb_appid,usb_ref1name,usb_ref1contact,usb_ref2name,usb_ref2contact,usb_item1,usb_value1,usb_item2,usb_value2,usb_item3,usb_value3,usb_item4,usb_value4,usb_guarantor,usb_otherIncome,comesfrom,IdenticalID)VALUES(");
                        sbSql.AppendFormat("{0},", app_id);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref1name"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref1name"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref1contact"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref1contact"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref2name"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref2name"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_ref2contact"))
                            sbSql.AppendFormat("'{0}',", dr["usb_ref2contact"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item1"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item1"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value1"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value1"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item2"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item2"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value2"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value2"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item3"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item3"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value3"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value3"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_item4"))
                            sbSql.AppendFormat("'{0}',", dr["usb_item4"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_value4"))
                            sbSql.AppendFormat("'{0}',", dr["usb_value4"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_guarantor"))
                            sbSql.AppendFormat("'{0}',", dr["usb_guarantor"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_otherIncome"))
                            sbSql.AppendFormat("'{0}',", dr["usb_otherIncome"].ToString());
                        else
                            sbSql.AppendFormat("'{0}',", DBNull.Value);
                        sbSql.AppendFormat("'{0}',", comesfrom);
                        if (ds.Tables["usb_info"].Columns.Contains("usb_appid"))
                            sbSql.AppendFormat("'{0}');", dr["usb_appid"].ToString());
                        else
                            sbSql.AppendFormat("'{0}');", DBNull.Value);
                    }
                }
                #endregion

                cmd = new MySqlCommand(sbSql.ToString(), cn, transaction);
                cmd.ExecuteNonQuery();

                transaction.Commit();
                insertresult = true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            cn.Close();
            return insertresult;
        }

        public static DataSet ConvertJsontoDataSet(string jsonString)
        {
            DataSet ds = new DataSet();
            try
            {
                XmlDocument xd = new XmlDocument();
                xd = (XmlDocument)JsonConvert.DeserializeXmlNode("{\"root\":" + jsonString + "}", "root");
                ds.ReadXml(new XmlNodeReader(xd));
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return ds;
        }
    }
}
